﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class PPARDAO
    {
        public PPARDAO() { }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PPAR model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "PPAR");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PPAR(");
            strSql.Append("ID,PPARNO,PARTID,STATUS,PPARDATE,ISWITHOUTSAMPLE,DELIVERYPQ,DELIVERYCOMPANY,DELIVERYNOTEDATE,DELIVERYNOTENUMBER,SUBMISSIONLEVEL,ISCAPABILITYDOCUMENT,ISCAPABILITYPART,CAPABILITYFEEDBACK,PARTRELATEDRESULT,PROCESSRELATEDRESULT,FINALRESULT,PARTRELATEDRESULTREASON,PROCESSRELATEDRESULTREASON,FINALRESULTREASON,RESUBMISSIONDATE,DEVIATIONSHEET,DEVIATIONEXPIREDATE,SAMPLINGREPORTNO,SAMPLINGREPORTSTATUS,SAMPLINGREPORTFJID,SAMPLINGREPORTAUTHOR,QUANLITYENGINEER,QUANLITYMANAGER,QUANLITYVP,DECISIONTEXT,EXPLANATIONS,PPARVERSION,APPROVALSTATUSBYQM,APPROVALSTATUSBYVP,REASONDISAPPROVALBYQM,REASONDISAPPROVALBYBP,FJID)");
            strSql.Append(" values (");
            strSql.Append(":ID,:PPARNO,:PARTID,:STATUS,:PPARDATE,:ISWITHOUTSAMPLE,:DELIVERYPQ,:DELIVERYCOMPANY,:DELIVERYNOTEDATE,:DELIVERYNOTENUMBER,:SUBMISSIONLEVEL,:ISCAPABILITYDOCUMENT,:ISCAPABILITYPART,:CAPABILITYFEEDBACK,:PARTRELATEDRESULT,:PROCESSRELATEDRESULT,:FINALRESULT,:PARTRELATEDRESULTREASON,:PROCESSRELATEDRESULTREASON,:FINALRESULTREASON,:RESUBMISSIONDATE,:DEVIATIONSHEET,:DEVIATIONEXPIREDATE,:SAMPLINGREPORTNO,:SAMPLINGREPORTSTATUS,:SAMPLINGREPORTFJID,:SAMPLINGREPORTAUTHOR,:QUANLITYENGINEER,:QUANLITYMANAGER,:QUANLITYVP,:DECISIONTEXT,:EXPLANATIONS,:PPARVERSION,:APPROVALSTATUSBYQM,:APPROVALSTATUSBYVP,:REASONDISAPPROVALBYQM,:REASONDISAPPROVALBYBP,:FJID)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":PPARNO", OracleType.VarChar,50),
					new OracleParameter(":PARTID", OracleType.Number,4),
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":PPARDATE", OracleType.DateTime),
					new OracleParameter(":ISWITHOUTSAMPLE", OracleType.Number,4),
					new OracleParameter(":DELIVERYPQ", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYCOMPANY", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYNOTEDATE", OracleType.DateTime),
					new OracleParameter(":DELIVERYNOTENUMBER", OracleType.VarChar,50),
					new OracleParameter(":SUBMISSIONLEVEL", OracleType.Number,4),
					new OracleParameter(":ISCAPABILITYDOCUMENT", OracleType.Number,4),
					new OracleParameter(":ISCAPABILITYPART", OracleType.Number,4),
					new OracleParameter(":CAPABILITYFEEDBACK", OracleType.VarChar,50),
					new OracleParameter(":PARTRELATEDRESULT", OracleType.Char,1),
					new OracleParameter(":PROCESSRELATEDRESULT", OracleType.Char,1),
					new OracleParameter(":FINALRESULT", OracleType.Char,1),
					new OracleParameter(":PARTRELATEDRESULTREASON", OracleType.Char,1),
					new OracleParameter(":PROCESSRELATEDRESULTREASON", OracleType.Char,1),
					new OracleParameter(":FINALRESULTREASON", OracleType.Char,1),
					new OracleParameter(":RESUBMISSIONDATE", OracleType.DateTime),
					new OracleParameter(":DEVIATIONSHEET", OracleType.VarChar,50),
					new OracleParameter(":DEVIATIONEXPIREDATE", OracleType.DateTime),
					new OracleParameter(":SAMPLINGREPORTNO", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTSTATUS", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTFJID", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTAUTHOR", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYENGINEER", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYMANAGER", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYVP", OracleType.VarChar,50),
					new OracleParameter(":DECISIONTEXT", OracleType.VarChar,500),
					new OracleParameter(":EXPLANATIONS", OracleType.VarChar,500),
					new OracleParameter(":PPARVERSION", OracleType.VarChar,20),
					new OracleParameter(":APPROVALSTATUSBYQM", OracleType.Number,4),
					new OracleParameter(":APPROVALSTATUSBYVP", OracleType.Number,4),
					new OracleParameter(":REASONDISAPPROVALBYQM", OracleType.VarChar,50),
					new OracleParameter(":REASONDISAPPROVALBYBP", OracleType.VarChar,50),
					new OracleParameter(":FJID", OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.PPARNO;
            parameters[2].Value = model.PARTID;
            parameters[3].Value = model.STATUS;
            parameters[4].Value = model.PPARDATE;
            parameters[5].Value = model.ISWITHOUTSAMPLE;
            parameters[6].Value = model.DELIVERYPQ;
            parameters[7].Value = model.DELIVERYCOMPANY;
            parameters[8].Value = model.DELIVERYNOTEDATE;
            parameters[9].Value = model.DELIVERYNOTENUMBER;
            parameters[10].Value = model.SUBMISSIONLEVEL;
            parameters[11].Value = model.ISCAPABILITYDOCUMENT;
            parameters[12].Value = model.ISCAPABILITYPART;
            parameters[13].Value = model.CAPABILITYFEEDBACK;
            parameters[14].Value = model.PARTRELATEDRESULT;
            parameters[15].Value = model.PROCESSRELATEDRESULT;
            parameters[16].Value = model.FINALRESULT;
            parameters[17].Value = model.PARTRELATEDRESULTREASON;
            parameters[18].Value = model.PROCESSRELATEDRESULTREASON;
            parameters[19].Value = model.FINALRESULTREASON;
            parameters[20].Value = model.RESUBMISSIONDATE;
            parameters[21].Value = model.DEVIATIONSHEET;
            parameters[22].Value = model.DEVIATIONEXPIREDATE;
            parameters[23].Value = model.SAMPLINGREPORTNO;
            parameters[24].Value = model.SAMPLINGREPORTSTATUS;
            parameters[25].Value = model.SAMPLINGREPORTFJID;
            parameters[26].Value = model.SAMPLINGREPORTAUTHOR;
            parameters[27].Value = model.QUANLITYENGINEER;
            parameters[28].Value = model.QUANLITYMANAGER;
            parameters[29].Value = model.QUANLITYVP;
            parameters[30].Value = model.DECISIONTEXT;
            parameters[31].Value = model.EXPLANATIONS;
            parameters[32].Value = model.PPARVERSION;
            parameters[33].Value = model.APPROVALSTATUSBYQM;
            parameters[34].Value = model.APPROVALSTATUSBYVP;
            parameters[35].Value = model.REASONDISAPPROVALBYQM;
            parameters[36].Value = model.REASONDISAPPROVALBYBP;
            parameters[37].Value = model.FJID;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters) > 0 ? int.Parse(model.ID.ToString()) : 0;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int AddPP(PPAR model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "PPAR");
            model.PPARNO = GetPPARNo(DbHelperOra.GetMaxID("pparno", "PPAR"));
            model.SAMPLINGREPORTNO = GetReportNo(DbHelperOra.GetMaxID("SAMPLINGREPORTNO", "PPAR"));
            model.PPARVERSION = "01";
            model.FJID = Guid.NewGuid().ToString();
            model.DEVIATIONSHEET = Guid.NewGuid().ToString();
            model.QEFJID = Guid.NewGuid().ToString();
            model.FINALSTATUS = 0;
            model.SUBMISSIONLEVEL = 3;
            model.ISSPECIFICATIONS = 1;
            model.Reportvesion = "00";
            model.Issendmail = 0;
            model.IsInapp = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PPAR(");
            strSql.Append("ID,PARTID,STATUS,PPARNO,PPARVERSION,FJID,DEVIATIONSHEET,FINALSTATUS,SAMPLINGREPORTNO,SUBMISSIONLEVEL,ISSPECIFICATIONS,QEFJID,REPORTVESION,ISSENDMAIL,PLANEDPPARDATE,qeuser,ISINAPP)");
            strSql.Append(" values (");
            strSql.Append(":ID,:PARTID,:STATUS,:PPARNO,:PPARVERSION,:FJID,:DEVIATIONSHEET,:FINALSTATUS,:SAMPLINGREPORTNO,:SUBMISSIONLEVEL,:ISSPECIFICATIONS,:QEFJID,:REPORTVESION,:ISSENDMAIL,:PLANEDPPARDATE,:qeuser,:ISINAPP)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":PARTID", OracleType.Number,4),
					new OracleParameter(":STATUS", OracleType.Number,4),
                                           //new OracleParameter(":PPARDATE",OracleType.DateTime),
                                           new OracleParameter(":PPARNO",OracleType.VarChar,50),
                                           new OracleParameter(":PPARVERSION",OracleType.VarChar,50),
                                           new OracleParameter(":FJID",OracleType.VarChar,50),
                                           new OracleParameter(":DEVIATIONSHEET",OracleType.VarChar,50),
                                           new OracleParameter(":FINALSTATUS",OracleType.Number,4),
                                           new OracleParameter(":SAMPLINGREPORTNO",OracleType.VarChar,50),
                                           new OracleParameter(":SUBMISSIONLEVEL",OracleType.Number,4),
                                           new OracleParameter(":ISSPECIFICATIONS",OracleType.Number,4),
                                           new OracleParameter(":QEFJID",OracleType.NVarChar,50),
                                           new OracleParameter(":REPORTVESION",OracleType.VarChar,50),
                                           new OracleParameter(":ISSENDMAIL",OracleType.Number,4),
                                           new OracleParameter(":PLANEDPPARDATE",OracleType.DateTime),
                                           new OracleParameter(":qeuser",OracleType.VarChar,50),
                                           new OracleParameter(":ISINAPP",OracleType.Number,4)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.PARTID;
            parameters[2].Value = model.STATUS;
            //parameters[3].Value = model.PPARDATE;
            parameters[3].Value = model.PPARNO;
            parameters[4].Value = model.PPARVERSION;
            parameters[5].Value = model.FJID;
            parameters[6].Value = model.DEVIATIONSHEET;
            parameters[7].Value = model.FINALSTATUS;
            parameters[8].Value = model.SAMPLINGREPORTNO;
            parameters[9].Value = model.SUBMISSIONLEVEL;
            parameters[10].Value = model.ISSPECIFICATIONS;
            parameters[11].Value = model.QEFJID;
            parameters[12].Value = model.Reportvesion;
            parameters[13].Value = model.Issendmail;
            parameters[14].Value = model.Planedppardate;
            parameters[15].Value = model.Qeuser;
            parameters[16].Value = model.IsInapp;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters) > 0 ? int.Parse(model.ID.ToString()) : 0;
        }

        /// <summary>
        /// 格式化ReportNO
        /// </summary>
        /// <param name="MaxNo"></param>
        /// <returns></returns>
        private string GetReportNo(int MaxNo)
        {
            string NewNo = "";
            switch (MaxNo.ToString().Length)
            {
                case 1:
                    NewNo = "00000" + MaxNo;
                    break;
                case 2:
                    NewNo = "0000" + MaxNo;
                    break;
                case 3:
                    NewNo = "000" + MaxNo;
                    break;
                case 4:
                    NewNo = "00" + MaxNo;
                    break;
                case 5:
                    NewNo = "0" + MaxNo;
                    break;
                default:
                    NewNo = MaxNo.ToString();
                    break;
            }
            return NewNo;
        }

        /// <summary>
        /// 格式化PPARNO
        /// </summary>
        /// <param name="MaxNo"></param>
        /// <returns></returns>
        private string GetPPARNo(int MaxNo)
        {
            string NewNo = "";
            switch (MaxNo.ToString().Length)
            {
                case 1:
                    NewNo = "00000" + MaxNo;
                    break;
                case 2:
                    NewNo = "0000" + MaxNo;
                    break;
                case 3:
                    NewNo = "000" + MaxNo;
                    break;
                case 4:
                    NewNo = "00" + MaxNo;
                    break;
                case 5:
                    NewNo = "0" + MaxNo;
                    break;
                default:
                    NewNo = MaxNo.ToString();
                    break;
            }
            return NewNo;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(PPAR model)
        {
            model.ENDTIME = DateTime.Now;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PPAR set ");
            strSql.Append("ID=:ID,");
            strSql.Append("PPARNO=:PPARNO,");
            strSql.Append("PARTID=:PARTID,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("PPARDATE=:PPARDATE,");
            strSql.Append("ISWITHOUTSAMPLE=:ISWITHOUTSAMPLE,");
            strSql.Append("DELIVERYPQ=:DELIVERYPQ,");
            strSql.Append("DELIVERYCOMPANY=:DELIVERYCOMPANY,");
            strSql.Append("DELIVERYNOTEDATE=:DELIVERYNOTEDATE,");
            strSql.Append("DELIVERYNOTENUMBER=:DELIVERYNOTENUMBER,");
            strSql.Append("SUBMISSIONLEVEL=:SUBMISSIONLEVEL,");
            strSql.Append("ISCAPABILITYDOCUMENT=:ISCAPABILITYDOCUMENT,");
            strSql.Append("ISCAPABILITYPART=:ISCAPABILITYPART,");
            strSql.Append("CAPABILITYFEEDBACK=:CAPABILITYFEEDBACK,");
            strSql.Append("PARTRELATEDRESULT=:PARTRELATEDRESULT,");
            strSql.Append("PROCESSRELATEDRESULT=:PROCESSRELATEDRESULT,");
            strSql.Append("FINALRESULT=:FINALRESULT,");
            strSql.Append("PARTRELATEDRESULTREASON=:PARTRELATEDRESULTREASON,");
            strSql.Append("PROCESSRELATEDRESULTREASON=:PROCESSRELATEDRESULTREASON,");
            strSql.Append("FINALRESULTREASON=:FINALRESULTREASON,");
            strSql.Append("RESUBMISSIONDATE=:RESUBMISSIONDATE,");
            strSql.Append("DEVIATIONSHEET=:DEVIATIONSHEET,");
            strSql.Append("DEVIATIONEXPIREDATE=:DEVIATIONEXPIREDATE,");
            strSql.Append("SAMPLINGREPORTNO=:SAMPLINGREPORTNO,");
            strSql.Append("SAMPLINGREPORTSTATUS=:SAMPLINGREPORTSTATUS,");
            strSql.Append("SAMPLINGREPORTFJID=:SAMPLINGREPORTFJID,");
            strSql.Append("SAMPLINGREPORTAUTHOR=:SAMPLINGREPORTAUTHOR,");
            strSql.Append("QUANLITYENGINEER=:QUANLITYENGINEER,");
            strSql.Append("QUANLITYMANAGER=:QUANLITYMANAGER,");
            strSql.Append("QUANLITYVP=:QUANLITYVP,");
            strSql.Append("DECISIONTEXT=:DECISIONTEXT,");
            strSql.Append("EXPLANATIONS=:EXPLANATIONS,");
            strSql.Append("PPARVERSION=:PPARVERSION,");
            strSql.Append("APPROVALSTATUSBYQM=:APPROVALSTATUSBYQM,");
            strSql.Append("APPROVALSTATUSBYVP=:APPROVALSTATUSBYVP,");
            strSql.Append("REASONDISAPPROVALBYQM=:REASONDISAPPROVALBYQM,");
            strSql.Append("REASONDISAPPROVALBYBP=:REASONDISAPPROVALBYBP,");
            strSql.Append("FJID=:FJID,");
            strSql.Append("FINALSTATUS=:FINALSTATUS,ENDTIME=:ENDTIME,REPORTVESION=:REPORTVESION");
            strSql.Append(" where id=" + model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":PPARNO", OracleType.VarChar,50),
					new OracleParameter(":PARTID", OracleType.Number,4),
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":PPARDATE", OracleType.DateTime),
					new OracleParameter(":ISWITHOUTSAMPLE", OracleType.Number,4),
					new OracleParameter(":DELIVERYPQ", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYCOMPANY", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYNOTEDATE", OracleType.DateTime),
					new OracleParameter(":DELIVERYNOTENUMBER", OracleType.VarChar,50),
					new OracleParameter(":SUBMISSIONLEVEL", OracleType.Number,4),
					new OracleParameter(":ISCAPABILITYDOCUMENT", OracleType.Number,4),
					new OracleParameter(":ISCAPABILITYPART", OracleType.Number,4),
					new OracleParameter(":CAPABILITYFEEDBACK", OracleType.VarChar,50),
					new OracleParameter(":PARTRELATEDRESULT", OracleType.Char,1),
					new OracleParameter(":PROCESSRELATEDRESULT", OracleType.Char,1),
					new OracleParameter(":FINALRESULT", OracleType.Char,1),
					new OracleParameter(":PARTRELATEDRESULTREASON", OracleType.Char,50),
					new OracleParameter(":PROCESSRELATEDRESULTREASON", OracleType.Char,50),
					new OracleParameter(":FINALRESULTREASON", OracleType.Char,50),
					new OracleParameter(":RESUBMISSIONDATE", OracleType.DateTime),
					new OracleParameter(":DEVIATIONSHEET", OracleType.VarChar,50),
					new OracleParameter(":DEVIATIONEXPIREDATE", OracleType.DateTime),
					new OracleParameter(":SAMPLINGREPORTNO", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTSTATUS", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTFJID", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGREPORTAUTHOR", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYENGINEER", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYMANAGER", OracleType.VarChar,50),
					new OracleParameter(":QUANLITYVP", OracleType.VarChar,50),
					new OracleParameter(":DECISIONTEXT", OracleType.VarChar,500),
					new OracleParameter(":EXPLANATIONS", OracleType.VarChar,500),
					new OracleParameter(":PPARVERSION", OracleType.VarChar,20),
					new OracleParameter(":APPROVALSTATUSBYQM", OracleType.Number,4),
					new OracleParameter(":APPROVALSTATUSBYVP", OracleType.Number,4),
					new OracleParameter(":REASONDISAPPROVALBYQM", OracleType.VarChar,50),
					new OracleParameter(":REASONDISAPPROVALBYBP", OracleType.VarChar,50),
					new OracleParameter(":FJID", OracleType.VarChar,50),
                                           new OracleParameter(":FINALSTATUS",OracleType.Number,4),
                                           new OracleParameter(":ENDTIME",OracleType.DateTime),
                                           new OracleParameter(":REPORTVESION",OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.PPARNO;
            parameters[2].Value = model.PARTID;
            parameters[3].Value = model.STATUS;
            parameters[4].Value = model.PPARDATE;
            parameters[5].Value = model.ISWITHOUTSAMPLE;
            parameters[6].Value = model.DELIVERYPQ;
            parameters[7].Value = model.DELIVERYCOMPANY;
            parameters[8].Value = model.DELIVERYNOTEDATE;
            parameters[9].Value = model.DELIVERYNOTENUMBER;
            parameters[10].Value = model.SUBMISSIONLEVEL;
            parameters[11].Value = model.ISCAPABILITYDOCUMENT;
            parameters[12].Value = model.ISCAPABILITYPART;
            parameters[13].Value = model.CAPABILITYFEEDBACK;
            parameters[14].Value = model.PARTRELATEDRESULT;
            parameters[15].Value = model.PROCESSRELATEDRESULT;
            parameters[16].Value = model.FINALRESULT;
            parameters[17].Value = model.PARTRELATEDRESULTREASON;
            parameters[18].Value = model.PROCESSRELATEDRESULTREASON;
            parameters[19].Value = model.FINALRESULTREASON;
            parameters[20].Value = model.RESUBMISSIONDATE;
            parameters[21].Value = model.DEVIATIONSHEET;
            parameters[22].Value = model.DEVIATIONEXPIREDATE;
            parameters[23].Value = model.SAMPLINGREPORTNO;
            parameters[24].Value = model.SAMPLINGREPORTSTATUS;
            parameters[25].Value = model.SAMPLINGREPORTFJID;
            parameters[26].Value = model.SAMPLINGREPORTAUTHOR;
            parameters[27].Value = model.QUANLITYENGINEER;
            parameters[28].Value = model.QUANLITYMANAGER;
            parameters[29].Value = model.QUANLITYVP;
            parameters[30].Value = model.DECISIONTEXT;
            parameters[31].Value = model.EXPLANATIONS;
            parameters[32].Value = model.PPARVERSION;
            parameters[33].Value = model.APPROVALSTATUSBYQM;
            parameters[34].Value = model.APPROVALSTATUSBYVP;
            parameters[35].Value = model.REASONDISAPPROVALBYQM;
            parameters[36].Value = model.REASONDISAPPROVALBYBP;
            parameters[37].Value = model.FJID;
            parameters[38].Value = model.FINALSTATUS;
            parameters[39].Value = model.ENDTIME;
            parameters[40].Value = model.Reportvesion;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 供应商更新一条数据
        /// </summary>
        public int UpdateForSupplier(PPAR model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PPAR set ");
            strSql.Append("STATUS=:STATUS,");//
            strSql.Append("ISWITHOUTSAMPLE=:ISWITHOUTSAMPLE,"); //
            strSql.Append("DELIVERYPQ=:DELIVERYPQ,");//
            strSql.Append("DELIVERYCOMPANY=:DELIVERYCOMPANY,");//
            strSql.Append("DELIVERYNOTEDATE=:DELIVERYNOTEDATE,");//
            strSql.Append("DELIVERYNOTENUMBER=:DELIVERYNOTENUMBER,FINALSTATUS=0,"); //
            strSql.Append("PPARVERSION=:PPARVERSION,SUBMISSIONLEVEL=:SUBMISSIONLEVEL,ISSPECIFICATIONS=:ISSPECIFICATIONS,PPARDATE=:PPARDATE,SUBMITTEDBY=:SUBMITTEDBY"); //
            strSql.Append(" where id=" + model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":ISWITHOUTSAMPLE", OracleType.Number,4),
					new OracleParameter(":DELIVERYPQ", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYCOMPANY", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYNOTEDATE", OracleType.DateTime),
					new OracleParameter(":DELIVERYNOTENUMBER", OracleType.VarChar,50),
                    new OracleParameter(":PPARVERSION",OracleType.Char,50),
                    new OracleParameter(":SUBMISSIONLEVEL",OracleType.Number,4),
                    new OracleParameter(":ISSPECIFICATIONS",OracleType.Number,4),
                    new OracleParameter(":PPARDATE",OracleType.DateTime),
                    new OracleParameter(":SUBMITTEDBY",OracleType.VarChar,150)
					};
            parameters[0].Value = model.STATUS;
            parameters[1].Value = model.ISWITHOUTSAMPLE;
            parameters[2].Value = model.DELIVERYPQ;
            parameters[3].Value = model.DELIVERYCOMPANY;
            parameters[4].Value = model.DELIVERYNOTEDATE;
            parameters[5].Value = model.DELIVERYNOTENUMBER;
            parameters[6].Value = model.PPARVERSION;
            parameters[7].Value = model.SUBMISSIONLEVEL;
            parameters[8].Value = model.ISSPECIFICATIONS;
            parameters[9].Value = model.PPARDATE;
            parameters[10].Value = model.Submittedby;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// QE更新一条数据
        /// </summary>
        public int UpdateForQe(PPAR model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PPAR set ");
            strSql.Append("STATUS=:STATUS,");//
            strSql.Append("ISWITHOUTSAMPLE=:ISWITHOUTSAMPLE,"); //
            strSql.Append("DELIVERYPQ=:DELIVERYPQ,");//
            strSql.Append("DELIVERYCOMPANY=:DELIVERYCOMPANY,");//
            strSql.Append("DELIVERYNOTEDATE=:DELIVERYNOTEDATE,");//
            strSql.Append("DELIVERYNOTENUMBER=:DELIVERYNOTENUMBER,FINALSTATUS=0,ISCAPABILITYDOCUMENT=:ISCAPABILITYDOCUMENT,ISCAPABILITYPART=:ISCAPABILITYPART,CAPABILITYFEEDBACK=:CAPABILITYFEEDBACK,"); //
            strSql.Append("PPARVERSION=:PPARVERSION"); //
            strSql.Append(" where id=" + model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":ISWITHOUTSAMPLE", OracleType.Number,4),
					new OracleParameter(":DELIVERYPQ", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYCOMPANY", OracleType.VarChar,50),
					new OracleParameter(":DELIVERYNOTEDATE", OracleType.DateTime),
					new OracleParameter(":DELIVERYNOTENUMBER", OracleType.VarChar,50),
                    new OracleParameter(":PPARVERSION",OracleType.Char,50),
                    new OracleParameter(":ISCAPABILITYDOCUMENT",OracleType.Number,4),
                    new OracleParameter(":ISCAPABILITYPART",OracleType.Number,4),
                    new OracleParameter(":CAPABILITYFEEDBACK",OracleType.VarChar,50)
					};
            parameters[0].Value = model.STATUS;
            parameters[1].Value = model.ISWITHOUTSAMPLE;
            parameters[2].Value = model.DELIVERYPQ;
            parameters[3].Value = model.DELIVERYCOMPANY;
            parameters[4].Value = model.DELIVERYNOTEDATE;
            parameters[5].Value = model.DELIVERYNOTENUMBER;
            parameters[6].Value = model.PPARVERSION;
            parameters[7].Value = model.ISCAPABILITYDOCUMENT;
            parameters[8].Value = model.ISCAPABILITYPART;
            parameters[9].Value = model.CAPABILITYFEEDBACK;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PPAR ");
            strSql.Append(" where id=" + id);
            OracleParameter[] parameters = {
};

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PPAR GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PPARNO,PARTID,STATUS,PPARDATE,ISWITHOUTSAMPLE,DELIVERYPQ,DELIVERYCOMPANY,DELIVERYNOTEDATE,DELIVERYNOTENUMBER,SUBMISSIONLEVEL,ISCAPABILITYDOCUMENT,ISCAPABILITYPART,CAPABILITYFEEDBACK,PARTRELATEDRESULT,PROCESSRELATEDRESULT,FINALRESULT,PARTRELATEDRESULTREASON,PROCESSRELATEDRESULTREASON,FINALRESULTREASON,RESUBMISSIONDATE,DEVIATIONSHEET,DEVIATIONEXPIREDATE,SAMPLINGREPORTNO,SAMPLINGREPORTSTATUS,SAMPLINGREPORTFJID,SAMPLINGREPORTAUTHOR,QUANLITYENGINEER,QUANLITYMANAGER,QUANLITYVP,DECISIONTEXT,EXPLANATIONS,PPARVERSION,APPROVALSTATUSBYQM,APPROVALSTATUSBYVP,REASONDISAPPROVALBYQM,REASONDISAPPROVALBYBP,FJID,FINALSTATUS,ISSPECIFICATIONS,QEFJID,REPORTVESION,SUBMITTEDBY from PPAR  ");
            strSql.Append(" where id=" + id);
            OracleParameter[] parameters = {
};

            PPAR model = new PPAR();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.PPARNO = ds.Tables[0].Rows[0]["PPARNO"].ToString();
                if (ds.Tables[0].Rows[0]["PARTID"].ToString() != "")
                {
                    model.PARTID = int.Parse(ds.Tables[0].Rows[0]["PARTID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["STATUS"].ToString() != "")
                {
                    model.STATUS = int.Parse(ds.Tables[0].Rows[0]["STATUS"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PPARDATE"].ToString() != "")
                {
                    model.PPARDATE = DateTime.Parse(ds.Tables[0].Rows[0]["PPARDATE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISWITHOUTSAMPLE"].ToString() != "")
                {
                    model.ISWITHOUTSAMPLE = int.Parse(ds.Tables[0].Rows[0]["ISWITHOUTSAMPLE"].ToString());
                }
                model.DELIVERYPQ = ds.Tables[0].Rows[0]["DELIVERYPQ"].ToString();
                model.DELIVERYCOMPANY = ds.Tables[0].Rows[0]["DELIVERYCOMPANY"].ToString();
                if (ds.Tables[0].Rows[0]["DELIVERYNOTEDATE"].ToString() != "")
                {
                    model.DELIVERYNOTEDATE = DateTime.Parse(ds.Tables[0].Rows[0]["DELIVERYNOTEDATE"].ToString());
                }
                model.DELIVERYNOTENUMBER = ds.Tables[0].Rows[0]["DELIVERYNOTENUMBER"].ToString();
                if (ds.Tables[0].Rows[0]["SUBMISSIONLEVEL"].ToString() != "")
                {
                    model.SUBMISSIONLEVEL = int.Parse(ds.Tables[0].Rows[0]["SUBMISSIONLEVEL"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISCAPABILITYDOCUMENT"].ToString() != "")
                {
                    model.ISCAPABILITYDOCUMENT = int.Parse(ds.Tables[0].Rows[0]["ISCAPABILITYDOCUMENT"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISCAPABILITYPART"].ToString() != "")
                {
                    model.ISCAPABILITYPART = int.Parse(ds.Tables[0].Rows[0]["ISCAPABILITYPART"].ToString());
                }
                model.CAPABILITYFEEDBACK = ds.Tables[0].Rows[0]["CAPABILITYFEEDBACK"].ToString();
                model.PARTRELATEDRESULT = ds.Tables[0].Rows[0]["PARTRELATEDRESULT"].ToString();
                model.PROCESSRELATEDRESULT = ds.Tables[0].Rows[0]["PROCESSRELATEDRESULT"].ToString();
                model.FINALRESULT = ds.Tables[0].Rows[0]["FINALRESULT"].ToString();
                model.PARTRELATEDRESULTREASON = ds.Tables[0].Rows[0]["PARTRELATEDRESULTREASON"].ToString();
                model.PROCESSRELATEDRESULTREASON = ds.Tables[0].Rows[0]["PROCESSRELATEDRESULTREASON"].ToString();
                model.FINALRESULTREASON = ds.Tables[0].Rows[0]["FINALRESULTREASON"].ToString();
                if (ds.Tables[0].Rows[0]["RESUBMISSIONDATE"].ToString() != "")
                {
                    model.RESUBMISSIONDATE = DateTime.Parse(ds.Tables[0].Rows[0]["RESUBMISSIONDATE"].ToString());
                }
                model.DEVIATIONSHEET = ds.Tables[0].Rows[0]["DEVIATIONSHEET"].ToString();
                if (ds.Tables[0].Rows[0]["DEVIATIONEXPIREDATE"].ToString() != "")
                {
                    model.DEVIATIONEXPIREDATE = DateTime.Parse(ds.Tables[0].Rows[0]["DEVIATIONEXPIREDATE"].ToString());
                }
                model.SAMPLINGREPORTNO = ds.Tables[0].Rows[0]["SAMPLINGREPORTNO"].ToString();
                model.SAMPLINGREPORTSTATUS = ds.Tables[0].Rows[0]["SAMPLINGREPORTSTATUS"].ToString();
                model.SAMPLINGREPORTFJID = ds.Tables[0].Rows[0]["SAMPLINGREPORTFJID"].ToString();
                model.SAMPLINGREPORTAUTHOR = ds.Tables[0].Rows[0]["SAMPLINGREPORTAUTHOR"].ToString();
                model.QUANLITYENGINEER = ds.Tables[0].Rows[0]["QUANLITYENGINEER"].ToString();
                model.QUANLITYMANAGER = ds.Tables[0].Rows[0]["QUANLITYMANAGER"].ToString();
                model.QUANLITYVP = ds.Tables[0].Rows[0]["QUANLITYVP"].ToString();
                model.DECISIONTEXT = ds.Tables[0].Rows[0]["DECISIONTEXT"].ToString();
                model.EXPLANATIONS = ds.Tables[0].Rows[0]["EXPLANATIONS"].ToString();
                model.PPARVERSION = ds.Tables[0].Rows[0]["PPARVERSION"].ToString();
                if (ds.Tables[0].Rows[0]["APPROVALSTATUSBYQM"].ToString() != "")
                {
                    model.APPROVALSTATUSBYQM = int.Parse(ds.Tables[0].Rows[0]["APPROVALSTATUSBYQM"].ToString());
                }
                if (ds.Tables[0].Rows[0]["APPROVALSTATUSBYVP"].ToString() != "")
                {
                    model.APPROVALSTATUSBYVP = int.Parse(ds.Tables[0].Rows[0]["APPROVALSTATUSBYVP"].ToString());
                }
                model.REASONDISAPPROVALBYQM = ds.Tables[0].Rows[0]["REASONDISAPPROVALBYQM"].ToString();
                model.REASONDISAPPROVALBYBP = ds.Tables[0].Rows[0]["REASONDISAPPROVALBYBP"].ToString();
                model.FJID = ds.Tables[0].Rows[0]["FJID"].ToString();
                model.FINALSTATUS = int.Parse(ds.Tables[0].Rows[0]["FINALSTATUS"].ToString());
                model.ISSPECIFICATIONS = int.Parse(ds.Tables[0].Rows[0]["ISSPECIFICATIONS"].ToString());
                model.QEFJID = ds.Tables[0].Rows[0]["QEFJID"].ToString();
                model.Reportvesion = ds.Tables[0].Rows[0]["REPORTVESION"].ToString();
                model.Submittedby = ds.Tables[0].Rows[0]["SUBMITTEDBY"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PPARNO,PARTID,STATUS,PPARDATE,ISWITHOUTSAMPLE,DELIVERYPQ,DELIVERYCOMPANY,DELIVERYNOTEDATE,DELIVERYNOTENUMBER,SUBMISSIONLEVEL,ISCAPABILITYDOCUMENT,ISCAPABILITYPART,CAPABILITYFEEDBACK,PARTRELATEDRESULT,PROCESSRELATEDRESULT,FINALRESULT,PARTRELATEDRESULTREASON,PROCESSRELATEDRESULTREASON,FINALRESULTREASON,RESUBMISSIONDATE,DEVIATIONSHEET,DEVIATIONEXPIREDATE,SAMPLINGREPORTNO,SAMPLINGREPORTSTATUS,SAMPLINGREPORTFJID,SAMPLINGREPORTAUTHOR,QUANLITYENGINEER,QUANLITYMANAGER,QUANLITYVP,DECISIONTEXT,EXPLANATIONS,PPARVERSION,APPROVALSTATUSBYQM,APPROVALSTATUSBYVP,REASONDISAPPROVALBYQM,REASONDISAPPROVALBYBP,FJID  ");
            strSql.Append(" FROM PPAR ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListForSupplier(string SupplierID)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.planedppardate,CAPABILITYFEEDBACK,r.color,a.resubmissiondate from ppar a left join part r on a.partid=r.id
where r.supplierid={0} and( a.status<2 or a.status=4) order by a.id)t", SupplierID);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得数据列表 供应商修改后，part属工厂
        /// </summary>
        public DataSet GetListForSupplier2(string plantid)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.planedppardate,CAPABILITYFEEDBACK,r.color,a.resubmissiondate from ppar a left join part r on a.partid=r.id
where r.plantid={0} and a.isinapp=1  and( a.status<2 or a.status=4) order by a.id)t", plantid);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得数据列表 供应商修改后，管理员可以提交所有part
        /// </summary>
        public DataSet GetListForSupplier3(string supplieradmin)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.planedppardate,CAPABILITYFEEDBACK,r.color,a.resubmissiondate from ppar a left join part r on a.partid=r.id
where r.supplieradmin='{0}' and a.isinapp=1  and( a.status<2 or a.status=4) order by a.id)t", supplieradmin);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// CreatePPAR
        /// </summary>
        public DataSet GetListForCreate(string qeuser)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.FINALSTATUS,s.suppliername,s.suppliercode,r.color,n.receivedate from ppar a left join part r on a.partid=r.id
left join SUBSTITUTES u on a.qeuser=u.username  and u.substitutesenable=1  and u.substitutesdate>=sysdate --{0}
left join supplier s on r.supplierid=s.id
left join sign n on a.id=n.pparid
where  a.status>=2 and a.status<7 and a.status!=4 and finalstatus=0 and( a.qeuser='{1}' or u.substitutesuser='{1}')  order by a.id)t", DateTime.Now, qeuser);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// Inapplicable
        /// </summary>
        public DataSet GetListForInapplicable(string qeuser)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.FINALSTATUS,s.suppliername,s.suppliercode,r.color,n.receivedate from ppar a left join part r on a.partid=r.id
left join SUBSTITUTES u on a.qeuser=u.username  and u.substitutesenable=1  and u.substitutesdate>=sysdate --{0}
left join supplier s on r.supplierid=s.id
left join sign n on a.id=n.pparid
where  ISINAPP=0 and( a.qeuser='{1}' or u.substitutesuser='{1}')  order by a.id)t", DateTime.Now, qeuser);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// Approval
        /// </summary>
        public DataSet GetListForApproval(string username)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,s.suppliername,s.suppliercode,r.color,n.receivedate from ppar a left join part r on a.partid=r.id
left join users u on a.quanlityengineer=u.username
left join supplier s on r.supplierid=s.id
left join sign n on a.id=n.pparid
where  a.status>=2 and a.status<7 and a.status!=4 and finalstatus=1 and u.superior='" + username + "' order by a.id)t");

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// ApprovalVP  任何一个VP审核即可
        /// </summary>
        public DataSet GetListForApprovalVP(string username)
        {
            string sql1 = "select * from users where username='" + username + "' and country='Germany'";
            DataSet ds1 = DbHelperOra.Query(sql1);
            string sql2 = @"select * from users s left join substitutes u on  s.username=u.username  where u.substitutesenable=1  
and u.substitutesdate>=sysdate 
and s.country='Germany' and u.substitutesuser='" + username + "'";
            DataSet ds2 = DbHelperOra.Query(sql2);
            string sql = "";
            if (ds1.Tables[0].Rows.Count > 0 || ds2.Tables[0].Rows.Count > 0)
                sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,s.suppliername,s.suppliercode,r.color from ppar a left join part r on a.partid=r.id
left join users u on a.quanlitymanager=u.username
left join supplier s on r.supplierid=s.id
where  a.status>=2 and a.status<7 and a.status!=4 and finalstatus=2 
--and u.superior='{0}' 
order by a.id)t", username);
            else
                sql =
                    string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,s.suppliername,s.suppliercode,r.color from ppar a left join part r on a.partid=r.id
left join users u on a.quanlitymanager=u.username
left join supplier s on r.supplierid=s.id
where  a.status>=2 and a.status<7 and a.status!=4 and finalstatus=2 
and 1!=2
order by a.id)t", username);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得已完成数据列表
        /// </summary>
        public DataSet GetListForSupplierDecided(string SupplierID)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,FINALRESULT,a.resubmissiondate,r.color from ppar a left join part r on a.partid=r.id
where r.supplierid={0} and a.status=7 order by a.id)t", SupplierID);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得已完成数据列表 供应商修改后，part属于工厂
        /// </summary>
        public DataSet GetListForSupplierDecided2(string Plantid)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,FINALRESULT,a.resubmissiondate,r.color from ppar a left join part r on a.partid=r.id
where r.plantid={0} and a.status=7 and a.isinapp=1 order by a.id)t", Plantid);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得已完成数据列表 供应商修改后，供应商管理员
        /// </summary>
        public DataSet GetListForSupplierDecided3(string supplieradmin)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,FINALRESULT,a.resubmissiondate,r.color from ppar a left join part r on a.partid=r.id
where r.supplieradmin='{0}' and a.status=7 and a.isinapp=1  order by a.id)t", supplieradmin);

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// Report
        /// </summary>
        public DataSet GetListForReport(string PPARid)
        {
            string sql = string.Format(@"select rownum,a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.decisiontext,a.explanations,
FINALRESULTREASON,a.resubmissiondate,s.suppliername,s.dsc,s.contacttel,s.address,s.suppliercode,a.endtime,s.suppliername,s.postalcode from ppar a left join part r on a.partid=r.id
left join supplier s on s.id=r.supplierid where a.id={0}", PPARid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListForPPARid(string PPARid)
        {
            string sql = string.Format(@"select t.*,rownum from(select a.id,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,color,s.suppliername,s.suppliercode,a.deliverypq,a.deliverycompany,a.deliverynotedate,a.deliverynotenumber,a.FINALSTATUS,c.firstname,c.name,c.email,c.telephone from ppar a left join part r on a.partid=r.id left join supplier s on r.supplierid=s.id
left join contactperson c on a.submittedby=c.email
where a.id={0} order by a.id)t", PPARid);
            return DbHelperOra.Query(sql);
        }


        /// <summary>
        /// 根据pparid获取供应商所有工厂
        /// </summary>
        /// <param name="PPARid"></param>
        /// <returns></returns>
        public DataSet GetPlantListForPPARid(string PPARid)
        {
            string sql = string.Format(@"select p.* from ppar a
                                        left join  part r on a.partid=r.id
                                        left join plant p on r.supplieradmin=p.supplieradmin
                                        where a.id=" + PPARid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据供应商ID获取供应商所有工厂
        /// </summary>
        /// <param name="PPARid"></param>
        /// <returns></returns>
        public DataSet GetPlantListForSupplierid(string Supplierid)
        {
            string sql = "";
            if (Supplierid != null)
                sql = string.Format(@"select * from plant where  supplieradmin='"+Supplierid+"'");
            else
                sql = "select * from plant ";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据ID查询工厂
        /// </summary>
        /// <param name="PlantId"></param>
        /// <returns></returns>
        public DataSet GetPlantListForPlantID(string PlantId)
        {
            string sql = "";
            sql = string.Format(@"select * from plant where id=" + PlantId);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// PPARHistory
        /// </summary>
        /// <param name="partno"></param>
        /// <param name="plant"></param>
        /// <param name="ok"></param>
        /// <param name="supplierid"></param>
        /// <returns></returns>
        public DataSet GetListForWhere(string partno, string plant, bool ok, string supplierid)
        {
            string sql = string.Format(@"select rownum,a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.decisiontext,a.explanations,
FINALRESULT,a.resubmissiondate,a.DEVIATIONEXPIREDATE,s.suppliername,s.dsc,s.contacttel,s.address,s.suppliercode,qlevel,remarks,a.pparno,PPARVERSION,SUPPLIERPARTNO from ppar a left join part r on a.partid=r.id
left join supplier s on s.id=r.supplierid left join plant p on plantid=p.id where 1=1 ");
            if (!ok)
            {
                sql += "  and p.plantname='" + plant + "'";
                if (partno != "")
                {
                    sql += " and bdntpartno='" + partno + "'";
                }
            }
            if (supplierid != null)
                sql += " and r.supplierid=" + supplierid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// PPARHistory
        /// </summary>
        /// <param name="partno"></param>
        /// <param name="plant"></param>
        /// <param name="ok"></param>
        /// <param name="supplierid"></param>
        /// <returns></returns>
        public DataSet GetListForWhere2(string strWhere)
        {
            string sql = string.Format(@"select rownum,a.id,r.bdntpartno,r.bdntpartname,r.color,a.pparno,PPARVERSION,qlevel,a.ppardate,a.endtime,FINALRESULT,a.qeuser,r.bdntrevisionstatus,a.status,a.decisiontext,a.explanations,
a.resubmissiondate,a.DEVIATIONEXPIREDATE,s.suppliername,s.dsc,s.contacttel,s.address,s.suppliercode,remarks,a.pparno,r.bdntrevisionno,SUPPLIERPARTNO from ppar a left join part r on a.partid=r.id
left join supplier s on s.id=r.supplierid left join plant p on plantid=p.id where 1=1 ");
            sql += strWhere;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获取PPARNO与PARTNO
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataTable GetNoForLog(string pparid)
        {
            string sql = "select p.pparno,a.bdntpartno from ppar p left join part a on p.partid=a.id where p.id=" + pparid;
            return DbHelperOra.Query(sql).Tables[0];
        }

        /// <summary>
        /// PPAR分配QE
        /// </summary>
        /// <returns></returns>
        public DataSet GetTaskAllocation()
        {
            string sql = @"select rownum,r.bdntpartno,a.id from ppar a left join part r on a.partid=r.id  where qeuser is null";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 分配QE
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        public int UpdateForPPARTaskAllocation(PPAR pp)
        {
            string sql = string.Format("update ppar set qeuser='{0}' where id={1} ", pp.Qeuser, pp.ID);
            return DbHelperOra.ExecuteSql(sql);
        }


        #region 查询统计报表
        /// <summary>
        /// PPAR history
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public DataSet GetPPARHistory(PPAP.Model.Search.PPARSearch ps)
        {
            string sql = string.Format(@"select rownum,a.id,r.bdntpartno,a.pparno,qlevel,'' ShowStatus,s.suppliercode,PPARVERSION,a.ppardate,a.endtime,'' d1,'' d2,FINALRESULT,remarks,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,r.ReasonForSampling,a.status,a.decisiontext,a.explanations,
a.resubmissiondate,s.suppliername,s.dsc,s.contacttel,s.address,a.pparno,SUPPLIERPARTNO 
from ppar a left join part r on a.partid=r.id
left join supplier s on s.id=r.supplierid left join plant p on plantid=p.id where 1=1 ");
            if (ps.PartNo != "")
                sql += " and r.bdntpartno='" + ps.PartNo + "'";
            if (ps.SupplierNo != "")
                sql += " and s.suppliercode='" + ps.SupplierNo + "'";
            if (ps.StartDate1 != DateTime.MinValue)
            {
                //sql += " and a.ppardate>='" + ps.StartDate1 + "'";
                sql += " and trunc(a.ppardate)>=to_date( substr('" + ps.StartDate1 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (ps.StartDate2 != DateTime.MinValue)
            {
                //sql += " and a.ppardate<='" + ps.StartDate2 + "'";
                sql += " and trunc(a.ppardate)<=to_date( substr('" + ps.StartDate2 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (ps.SamplingReason != "")
                sql += " and r.ReasonForSampling='" + ps.SamplingReason + "'";
            if (ps.Version != "")
                sql += " and PPARVERSION='" + ps.Version + "'";
            if (ps.Status >= 0)
                sql += " and a.status=" + ps.Status + "";
            if (ps.MyCase)
                sql += " and (a.QuanlityEngineer='" + ps.CreateUser + "'  or a.quanlityengineer='" + ps.CreateUser + "' or a.quanlitymanager='" + ps.CreateUser + "' or a.quanlityvp='" + ps.CreateUser + "' or a.qeuser='" + ps.CreateUser + "' )";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// Sampling inspection
        /// </summary>
        /// <param name="sis"></param>
        /// <returns></returns>
        public DataSet GetSamplingInspection(PPAP.Model.Search.SamplingInspectionSearch sis)
        {
            string sql = string.Format(@"select rownum, pp.pparno,p.bdntpartno, s.SupplierCode,sc.samplecardno,td.testitemname,sc.deptrep,'' ShowStatus,
                                            sc.id,sc.result,sc.starttime,sc.endtimeplan,'' d1,'' d2,sc.status,pp.id as rid
                                            from SampleCard sc
                                            left join PPAR pp on sc.pparid=pp.id
                                            left join Part p on p.id=pp.partid
                                            left join TestDepartment td on td.id=sc.testdepartmentid
                                            left join Supplier s on s.ID=p.SupplierID
                                            where 1=1  and sc.status!=9  ");
            if (sis.PartNo != "")
                sql += " and p.bdntpartno='" + sis.PartNo + "'";
            if (sis.SupplierNo != "")
                sql += " and s.SupplierCode like '%" + sis.SupplierNo + "'";
            if (sis.StartTime1 != DateTime.MinValue)
            {
                //sql += " and sc.StartTime>='" + sis.StartTime1 + "'";
                sql += " and trunc(sc.StartTime)>=to_date( substr('" + sis.StartTime1 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (sis.StartTime2 != DateTime.MinValue)
            {
                //sql += " and sc.StartTime<='" + sis.StartTime2 + "'";
                sql += " and trunc(sc.StartTime)<=to_date( substr('" + sis.StartTime2 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (sis.EndTime1 != DateTime.MinValue)
            {
                //sql += " and sc.EndTimePlan>='" + sis.EndTime1 + "'";
                sql += " and trunc(sc.EndTimePlan)>=to_date( substr('" + sis.EndTime1 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (sis.EndTime2 != DateTime.MinValue)
            {
                //sql += " and sc.EndTimePlan<='" + sis.EndTime2 + "'";
                sql += " and trunc(sc.EndTimePlan)<=to_date( substr('" + sis.EndTime2 + "',1,10), 'yyyy-mm-dd ') ";
            }
            if (sis.DepartmentID > 0)
                sql += " and sc.TestDepartmentID='" + sis.DepartmentID + "'";
            if (sis.DeptRep != "")
                sql += " and sc.DeptRep='" + sis.DeptRep + "'";
            if (sis.Status >= 0)
                sql += " and sc.status=" + sis.Status + "";
            if (sis.Result != "" && sis.Result != null)
                sql += " and sc.Result='" + sis.Result + "'";
            if (sis.MyCase)
                sql += " and (pp.QuanlityEngineer='" + sis.CreateUser + "' OR sc.DEPTREP='" + sis.CreateUser + "' or pp.quanlityengineer='" + sis.CreateUser + "' or pp.quanlitymanager='" + sis.CreateUser + "' or pp.quanlityvp='"+sis.CreateUser+"' or pp.qeuser='"+sis.CreateUser+"' )";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// Sampling Report
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetReportByPPARid(string pparid)
        {
            string sql = @"select 'AR-'||a.samplingreportno||'.'||a.REPORTVESION samplingreportno,s.suppliername,a.quanlityengineer,a.quanlitymanager,a.quanlityvp,a.decisiontext,a.explanations,r.remarks,s.address,s.postalcode,s.suppliername,s.suppliercode,r.supplierpartno,
s.contacttel,r.bdntrevisionstatus,a.endtime,d.department,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,a.finalresult,a.reasondisapprovalbybp,a.resubmissiondate,r.elevel,a.DEVIATIONSHEET   from ppar a left join part r on a.partid=r.id
                            left join supplier s on r.supplierid=s.id 
                            left join users u on a.quanlityvp =u.username
                            left join department d on u.departmentid=d.id where a.id=" + pparid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// ReportForSearch
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public DataSet GetReportForSearch(string sqlwhere)
        {
            string sql = @"select rownum,t.* from (select  'AR-'||a.samplingreportno||'.'||a.pparversion samplingreportno,'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,suppliercode,a.pparversion,a.status,'PPAR decided ' st ,a.ppardate,'' d1
 ,FINALSTATUS,reasonforsampling,a.id from ppar a left join part r on a.partid=r.id
                            left join supplier s on r.supplierid=s.id )t";
            sql += sqlwhere;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// General
        /// </summary>
        /// <param name="partno"></param>
        /// <param name="plant"></param>
        /// <param name="ok"></param>
        /// <param name="supplierid"></param>
        /// <returns></returns>
        public DataSet GetListForGeneral(string sqlwhere)
        {
            string sql = string.Format(@"select rownum,a.id,a.pparno,r.bdntpartno,r.bdntpartname,r.bdntrevisionno,r.bdntrevisionstatus,a.status,a.ppardate,a.decisiontext,a.explanations,
FINALRESULT,a.resubmissiondate,a.DEVIATIONEXPIREDATE,s.suppliername,s.dsc,s.contacttel,s.address,s.suppliercode,qlevel,remarks,a.pparno,PPARVERSION,SUPPLIERPARTNO,a.endtime from ppar a left join part r on a.partid=r.id
left join supplier s on s.id=r.supplierid left join plant p on plantid=p.id where 1=1 ");
            sql += sqlwhere;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 更改设置状态
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public int UpdateInapplicable(string pid)
        {
            string sql = "update ppar set isinapp=1 where id="+pid;
            return DbHelperOra.ExecuteSql(sql);
        }


        #endregion

    }
}
